package com.library.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.library.bean.Book;
import com.util.jdbc.JDBCUtils;

public class BookDAO {
	/**
	 * 添加用户功能
	 * 
	 * @param b
	 * @return
	 * @throws SQLException
	 */
	public int insert(Book b) throws SQLException {

		// 1. 得到连接
		Connection conn = JDBCUtils.getConnection();
		// 2. 得到查询对象
		// 3. 执行sql语句
		String sql = "insert into book values (null,?,?,?,?);";
		PreparedStatement stmt = conn.prepareStatement(sql);

		stmt.setString(1, b.getBook_code());
		stmt.setString(2, b.getBook_name());
		stmt.setInt(3, b.getBook_total());
		stmt.setInt(4, b.getLend_numl());

		int result = stmt.executeUpdate();

		// 4. 释放资源
		JDBCUtils.destory(null, stmt, conn);

		return result;
	}

	/**
	 * 列表用户功能
	 * 
	 * @return
	 * @throws SQLException
	 */
	public List<Book> findBookAll() throws SQLException {

		// 1. 得到连接
		Connection conn = JDBCUtils.getConnection();
		// 2. 得到查询对象
		Statement stmt = conn.createStatement();
		// 3. 执行sql语句
		String sql = "select * from book;";
		ResultSet rs = stmt.executeQuery(sql);
		System.out.println("图书id\t图书代码\t图书名\t\t\t库存总量\t借出书籍数量");
		List<Book> list = new ArrayList<>();
		while (rs.next()) {
			int book_id = rs.getInt("book_id"); // 如果传入String 通过列名获取
			String book_code = rs.getString("book_code");
			String book_name = rs.getString("book_name");
			int book_total = rs.getInt("book_total");
			int lend_num = rs.getInt("lend_num");
			System.out.println(book_id + "\t" + book_code + "\t" + book_name + "\t\t" + book_total + "\t" + lend_num);
			list.add(new Book(book_id, book_code, book_name, book_total, lend_num));
		}
		// 4. 释放资源
		JDBCUtils.destory(null, stmt, conn);

		return list;
	}

}
